package batch.promionet;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.commons.dbutils.ResultSetHandler;

import org.apache.log4j.*;
import java.sql.Connection;

import java.sql.*;
import java.io.*;
import java.util.HashMap;
import java.lang.Integer;

public class makeUsernoFromId
{
    private final String FILEPATH = "/home01/sfc/batch/promionet/data/";
    private final String ORAURL = "jdbc:oracle:thin:@eu-stgdb:1521:ORAEU";
    //private final String ORAURL = "jdbc:oracle:thin:@eu-anydb:1521:ORAEU";

    private String fileName = "";

    public makeUsernoFromId() {
        ;
    }

    public static void main(String [] args) {
        makeUsernoFromId obj = null;

        if(args == null || args.length < 1 || args.length > 1) {
            System.out.println("java makeUsernoFromId Exception == Missed FileName Parameter");
            System.exit(-1);
        }
        else{
            obj = new makeUsernoFromId();
            obj.fileName = args[0];
        }

        Logger.getLogger("promionet.makeUsernoFromId").info("( * ) Start.");
        Logger.getLogger("promionet.makeUsernoFromId").info("fileName == " + obj.fileName);
        obj.startPG();
        Logger.getLogger("promionet.makeUsernoFromId").info("( * ) End.");
    }

    public void startPG(){
        HashMap hData =  null;

        Connection conn = null;
        QueryRunner qRunner = null;

        String strID;

        int iNonUser = 0;

        String dataFileNm = "promionet_makeUsernoFromId.txt";

        OutputStreamWriter oswFile = null;

        StringBuffer sQuery = new StringBuffer("SELECT REG_COUNTRY, USER_NO FROM G_MB_MEMBER WHERE ID = ? ");

        int iSize = 0;

        try {
            hData = this.getInfo();

            if(hData == null && hData.size() < 1) {
                throw new Exception("No Data");
            }

            iSize = hData.size();

            oswFile = new OutputStreamWriter(new FileOutputStream(FILEPATH + dataFileNm, false), "ISO-8859-1");

            Class.forName("oracle.jdbc.driver.OracleDriver");
            conn = DriverManager.getConnection(ORAURL, "gsms", "any02gs");
            conn.setAutoCommit(true);

            qRunner = new QueryRunner();

            ResultSetHandler rsh = new ScalarHandler(2);

            for(int i=0;i<iSize;i++) {
                strID = (String)hData.get(new Integer(i));

                String sUserNo = String.valueOf(qRunner.query(conn, sQuery.toString(), strID, rsh));

                if(sUserNo.equals("0") || sUserNo.equals("null")){
                    Logger.getLogger("promionet.makeUsernoFromId").info("Missed ID == " + strID);
                    iNonUser++;
                }
                else{
                    oswFile.write(sUserNo + "\n");
                }

                if((i % 100) == 0) Logger.getLogger("promionet.makeUsernoFromId").info("here we are == " + i);
            }
        }
        catch(Exception ex) {
            ex.printStackTrace();

            Logger.getLogger("promionet.makeUsernoFromId").info(ex.toString());
            StackTraceElement[] stElement = ex.getStackTrace();
            for(int p=0; p<stElement.length;p++){
                Logger.getLogger("promionet.makeUsernoFromId").info(stElement[p].toString());
            }
        }
        finally {
            Logger.getLogger("promionet.makeUsernoFromId").info("Total == " + iSize);
            Logger.getLogger("promionet.makeUsernoFromId").info("iNonUser == " + iNonUser);

            if(conn != null) try { conn.close(); } catch (Exception ee) { ee.printStackTrace();  }
            if(oswFile != null) try { oswFile.close(); } catch (Exception ee) { ee.printStackTrace();  }
        }
    }

    public HashMap getInfo() throws Exception {
        HashMap hMap = new HashMap();
        BufferedReader in = null;
        String line = "";
        int i = 0;

        try {
            in = new BufferedReader(new FileReader(FILEPATH + fileName));

            while((line = in.readLine()) != null){
                hMap.put(new Integer(i), line);
                i++;
            }
        }
        finally {
            if(in != null) try { in.close(); } catch(Exception e) {}
        }

        return hMap;
    }
}
